/*
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 */
package utopiaintelpackage.database;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import main.CommonMethods;

import org.apache.log4j.Logger;

import utopiaintelpackage.kd.KD;
import botobjects.Province;
import database.DBConnectionFactory;

/**
 * A class that saves and retreives KD objects
 * 
 * 
 */
public class KDtoDB {
   public static KD getKD(final String kdloc) {
	  KD kd = null;
	  PreparedStatement ps = null;
	  ResultSet rs = null;
	  try {
		 ps = DBConnectionFactory.getConnectionHandler().getConnection().prepareStatement("SELECT * FROM kd WHERE loc = ?");
		 ps.setString(1, kdloc);
		 rs = ps.executeQuery();
		 if (rs.next()) {
			kd = new KD();
			kd.setName(rs.getString("name"));
			kd.setLoc(rs.getString("loc"));
			kd.setComment(rs.getString("comment"));
			kd.setDragon(rs.getString("dragon"));
			kd.setUpdated(new Date(rs.getTimestamp("updated").getTime()));
			kd.setStance(rs.getString("stance"));
			kd.setAtWar(rs.getBoolean("atwar"));
			kd.setShow(rs.getBoolean("showkd"));

			ps = DBConnectionFactory.getConnectionHandler().getConnection()
				  .prepareStatement("SELECT * FROM province WHERE kdloc = ?");
			ps.setString(1, kdloc);
			rs = ps.executeQuery();

			while (rs.next()) {
			   kd.addProvince(rs.getString("name"), rs.getString("race"), rs.getString("personality"), rs.getInt("land"),
					 rs.getInt("nw"), rs.getString("title"));
			}
		 }
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
	  }
	  return kd;
   }

   public static String saveKD(final KD kd) {
	  List<Province> provs = kd.getProvs();
	  PreparedStatement ps = null;
	  Statement stmt = null;
	  ResultSet rs = null;
	  String out = "";
	  try {
		 if (CommonMethods.kdExists(kd.getLoc())) {
			ps = DBConnectionFactory.getConnectionHandler().getConnection()
				  .prepareStatement("UPDATE kd SET " + "name = ?, updated = ?, stance = ?, atwar = ? WHERE loc = ?");
			ps.setString(1, kd.getName());
			ps.setTimestamp(2, new Timestamp(Calendar.getInstance().getTimeInMillis()));
			ps.setString(3, kd.getStance());
			ps.setBoolean(4, kd.isAtWar());
			ps.setString(5, kd.getLoc());
			ps.executeUpdate();
		 } else {
			ps = DBConnectionFactory.getConnectionHandler().getConnection()
				  .prepareStatement("INSERT INTO kd VALUES (?,?,?,?,?,?,?,false)");
			ps.setString(1, kd.getLoc().trim());
			ps.setString(2, kd.getName());
			ps.setString(3, "");
			ps.setString(4, kd.getStance());
			ps.setString(5, "");
			ps.setTimestamp(6, new Timestamp(Calendar.getInstance().getTimeInMillis()));
			ps.setBoolean(7, kd.isAtWar());
			ps.executeUpdate();
		 }
		 for (final Province prov : provs) {
			if (!"".equals(CommonMethods.provinceExists(prov.getName()))) {
			   ps = DBConnectionFactory
					 .getConnectionHandler()
					 .getConnection()
					 .prepareStatement("UPDATE province SET " + "race = ?, title = ?, kdloc = ?, land = ?, nw = ? WHERE name = ?");
			   ps.setString(1, prov.getRace());
			   ps.setString(2, prov.getTitle());
			   ps.setString(3, kd.getLoc());
			   ps.setInt(4, prov.getLand());
			   ps.setInt(5, prov.getNw());
			   ps.setString(6, prov.getName());
			   ps.executeUpdate();
			} else {
			   ps = DBConnectionFactory.getConnectionHandler().getConnection()
					 .prepareStatement("INSERT INTO province VALUES " + "(?,?,?,?,?,?,?,?,0,false,?)");
			   ps.setString(1, prov.getName());
			   ps.setString(2, prov.getRace());
			   ps.setString(3, "Unknown");
			   ps.setString(4, "Unknown");
			   ps.setString(5, prov.getTitle());
			   ps.setString(6, kd.getLoc());
			   ps.setInt(7, prov.getLand());
			   ps.setInt(8, prov.getNw());
			   ps.setTimestamp(9, new Timestamp(kd.getUpdated().getTime()));
			   ps.executeUpdate();
			}
		 }

		 stmt = DBConnectionFactory.getConnectionHandler().getConnection()
			   .createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
		 final String query = "SELECT * FROM province WHERE kdloc = '" + kd.getLoc() + "'";
		 rs = stmt.executeQuery(query);

		 final ArrayList<String> provnames = new ArrayList<String>();
		 for (final Province p : provs) {
			provnames.add(p.getName());
		 }
		 while (rs.next()) {
			if (!provnames.contains(rs.getString("name"))) {
			   rs.deleteRow();
			}
		 }
		 out = "KD added";
	  } catch (final SQLException e) {
		 Logger.getLogger("Error").error("", e);
	  } finally {
		 try {
			rs.close();
		 } catch (final Exception e) {
		 }
		 try {
			ps.close();
		 } catch (final Exception e) {
		 }
		 try {
			stmt.close();
		 } catch (final Exception e) {
		 }
	  }
	  return out;
   }
}
